package com.starinside.wp.repository.cookbook;

import com.starinside.wp.domain.cookbook.RecipeConfig;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface RecipeConfigRepository extends JpaRepository<RecipeConfig, Integer>, JpaSpecificationExecutor<RecipeConfig> {
    //selectRecipeTypeBySchoolId
//    select *
//            from ybb_dictionary
//    where type = 'recipe_type'
//    and (obj_id is null or obj_id =#{schoolid,jdbcType=INTEGER})
//    and  state !='99'
//    order by sort_id asc

    List<RecipeConfig> findAllByAreaIdAndTypeAndDeletedIsFalseOrderBySortId(String areaId, String type);
    List<RecipeConfig> findAllByAreaIdIsNullAndTypeAndDeletedIsFalseOrderBySortId(String type);

    //    selectDictionaryNameIsExit
//    select <include refid="Base_Column_List" />
//            from ybb_dictionary
//    where type = #{type,jdbcType=VARCHAR}
//    and state !='99'
//    and (obj_id is null or obj_id =#{schoolId,jdbcType=INTEGER})
//    and name =#{name,jdbcType=VARCHAR}
    RecipeConfig findFirstByAreaIdAndTypeAndNameAndDeletedIsFalse(String areaId, String type, String name);

    RecipeConfig findByIdAndAreaId(Long id, String areaId);

    // update ybb_dictionary set sort_id =sort_id + #{count} where type ='recipe_type' and obj_id=#{schoolId} and value=#{recipeId}
    @Query(value = "update tb_recipe_config set sort_id = ?3 where type ='recipe_type' and area_id=?1 and value=?2", nativeQuery = true)
    @Modifying
    @Transactional
    void updateRecipeTypeSort(String areaId, String recipeId, int sort);

    @Query(value = "update tb_recipe_config set  name = ?1 where area_id = ?2 and  type = ?3  and  value = ?4", nativeQuery = true)
    @Modifying
    @Transactional
    void updateConfigNameBySchoolIDAndTypeAndValue(String name, String areaId, String type, String value);

    @Modifying
    @Transactional
    int deleteAllByAreaIdAndTypeAndValue(String areaId, String type, String value);

    //    select <include refid="Base_Column_List" />
//            from ybb_dictionary
//    where  type = #{type,jdbcType=VARCHAR}
//    and  (obj_id =#{schoolId,jdbcType=INTEGER} or obj_id is null)
//    and value= #{value,jdbcType=VARCHAR}
//    and state !='99'
//    order by sort_id asc
    RecipeConfig findFirstByAreaIdAndTypeAndValueAndDeletedIsFalseOrderBySortId(String areaId, String type, String value);


    //      <select id="selectUpSortByShoolIdAndTypeAndSort" parameterType="java.util.Map"  resultMap="BaseResultMap">
//    select a.* from ybb_dictionary a
//    where a.type = #{type,jdbcType=VARCHAR}
//    and a.obj_id=#{schoolId,jdbcType=INTEGER}
//    and a.state !='99'
//    and a.sort_id  &lt; #{sort,jdbcType=INTEGER}
//    order by a.sort_id desc
//    limit 1
//  </select>
    RecipeConfig findFirstByAreaIdAndTypeAndSortIdLessThanAndDeletedIsFalseOrderBySortIdDesc(String areaId, String type, int sortId);

    //
//  <select id="selectDownSortByShoolIdAndTypeAndSort" parameterType="java.util.Map"  resultMap="BaseResultMap">
//    select a.* from ybb_dictionary a
//    where a.type = #{type,jdbcType=VARCHAR}
//    and a.obj_id=#{schoolId,jdbcType=INTEGER}
//    and a.state !='99'
//    and a.sort_id  > #{sort,jdbcType=INTEGER}
//    order by a.sort_id asc
//    limit 1
//  </select>
    RecipeConfig findFirstByAreaIdAndTypeAndSortIdGreaterThanAndDeletedIsFalseOrderBySortIdAsc(String areaId, String type, int sortId);

//      <select id="selectUpSortByShoolIdAndTypeAndSort" parameterType="java.util.Map"  resultMap="BaseResultMap">
//    select a.* from ybb_dictionary a
//    where a.type = #{type,jdbcType=VARCHAR}
//    and a.obj_id=#{schoolId,jdbcType=INTEGER}
//    and a.state !='99'
//    and a.sort_id  &lt; #{sort,jdbcType=INTEGER}
//    order by a.sort_id desc
//    limit 1
//  </select>
//      <update id="upRecipeTypeSort" parameterType="java.util.Map">
//    update ybb_dictionary set sort_id =sort_id + #{count} where type ='recipe_type' and obj_id=#{schoolId} and value=#{recipeId}
//  </update>
    @Query(value = "update tb_recipe_config set sort_id =sort_id + ?1 where type ='recipe_type' and area_id=?2 and value=?3", nativeQuery = true)
    @Modifying
    @Transactional
    void increaseRecipeTypeSort(int count, String areaId, String recipeId);


//   <update id="downRecipeTypeSort" parameterType="java.util.Map" >
//    update ybb_dictionary set sort_id =sort_id - #{count} where type ='recipe_type' and obj_id=#{schoolId} and value=#{recipeId}
//  </update>
    @Query(value = "update tb_recipe_config set sort_id =sort_id - ?1 where type ='recipe_type' and area_id=?2 and value=?3", nativeQuery = true)
    @Modifying
    @Transactional
    void decreaseRecipeTypeSort(int count, String areaId, String recipeId);


    RecipeConfig findByAreaIdAndNameAndType(String areaId, String name, String type);
}